Click Here!
home account info subscribe login search My ITKnowledge FAQ/help site map contact us


 
Brief Full
 Advanced
      Search
 Search Tips
To access the contents, click the chapter and section titles.

Oracle Performance Tuning and Optimization
(Publisher: Macmillan Computer Publishing)
Author(s): Edward Whalen
ISBN: 067230886x
Publication Date: 04/01/96

Bookmark It

Search this book:
 
Previous Table of Contents Next


Avoid Dynamic Growth

As stated earlier, you want to avoid the dynamic space management that causes additional overhead and transactional delays. To determine whether rollback segments are a problem, look in the dynamic performance table, V$ROLLSTAT. The following columns are of particular interest:

  EXTENTS: Number of rollback extents.
  RSSIZE: The size in bytes of the rollback segment.
  OPTSIZE: The size to which OPTIMAL was set.
  AVEACTIVE: The current average size of active extents. Active extents are defined as extents with uncommitted transaction data.
  AVESHRINK: The total size of free extents divided by the number of shrinks (see the second item following).
  EXTENDS: The number of times the rollback segment added an extent.
  SHRINKS: The number of times the rollback segment shrank. Each shrink may be one or more extents at a time.
  HWMSIZE: The high water mark of rollback segment size. This is the largest that the segment size ever grew to be.

You can look at these statistics by using a SQL statement such as this one:

SQL> SELECT substr(name,1,40), extents, rssize, aveactive, aveshrink, extends,
shrinks
  2  FROM v$rollname rn, v$rollstat rs
  3  WHERE rn.usn = rs.usn;

SUBSTR(NAME,1,40)               EXTENTS   RSSIZE AVEACTIVE AVESHRINK   EXTENDS
SHRINKS
------------------------------ -------- --------  --------  --------  --------
SYSTEM                                4   202752         0         0         0
0
RB_TEMP                              53   540672     23929         0         0
0
RB1                                   2   202752         0         0         0
0
RB2                                   2   202752     55193         0         0
0

If the average size is close to the size set for OPTIMAL, OPTIMAL is set correctly. If either extends or shrinks are high, you must increase the value for OPTIMAL.

Review of Rollback Segment Tuning

To optimize rollback segments, remember the following rules:

  Avoid using the default rollback segments in the SYSTEM tablespace. Create your own tablespace for rollback segments.
  Create approximately one rollback segment for every four concurrent transactions you expect to have.
  Create 10 to 20 extents for each rollback segment (except for short-running OLTP transactions).
  Assign large rollback segments to long-running queries. These queries may need a large rollback to reconstruct a read-consistent version of the data. This data must be available for the entire length of the transaction. The size of the rollback segments should be approximately 10 percent the size of the largest table.
  Assign large rollback segments for long transactions that modify a large amount of data. These transactions create large amounts of rollback information. The size of the rollback segments should be approximately 10 percent the size of the largest table.
  Avoid dynamic expansion and reduction of rollback space by monitoring rollback information.

Rollback segments should be monitored periodically because the applications and user activity changes. What is optimal for rollback segments today may degrade over time because of an increase in user activity or changes in application code.

Checking for Latch Contention

For a transaction to be completed, redo information must be written to the redo log. Until this write has occurred, there is danger of losing the transaction should some sort of instance failure occur. To avoid this condition, a COMMIT is not completed until the redo record has been written.

Any kind of bottleneck in the redo log can cause performance problems for every process on the system. To make sure that this does not happen, check for contention on the redo log buffer latches as well as contention on the redo log buffers.

Redo Log Buffer Contention

To check for contention on the redo log buffer, simply check the dynamic performance table, V$SYSSTAT, for redo log space requests. If this number is not zero, this indicates that a process had to wait for space in the redo log buffer; the size of the redo log buffer should be increased. Check for this condition with the following SQL statement:

SQL> SELECT name, value
  2  FROM v$sysstat
  3  WHERE name = 'redo log space requests';

NAME                                                                 VALUE
----------------------------------------------------------------  --------
redo log space requests                                                  0

If this value is not 0, increase the initialization parameter LOG_BUFFER by 5 to 10 percent until your system runs with redo log space requests close to 0.


Previous Table of Contents Next


Products |  Contact Us |  About Us |  Privacy  |  Ad Info  |  Home

Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc.
All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited.